﻿
Imports System.Data.SqlClient                                           ' FOR SQL CONNECTION AND COMMAND.
Imports Excel = Microsoft.Office.Interop.Excel                                  ' EXCEL APPLICATION.
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat      ' TO AUTOFORMAT THE SHEET.
Public Class UCExport
    Dim connect As New connect()
    Private Sub UCExport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        LoadItem()

    End Sub
    Private Sub LoadItem()
        Dim ds As New DataSet
        ds = connect.GetDataSet("select * from INFORMATION_SCHEMA.tables")
        With cmbTableName
            .DataSource = ds.Tables(0)
            .DisplayMember = "TABLE_NAME"
            .ValueMember = "TABLE_NAME"
        End With
    End Sub
    Private Sub LoadData(ByVal tablename As String)
        Dim dt As New DataTable
        dt = connect.GetData("select * from " + tablename)
        dgvData.DataSource = dt
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub ExportGridToExcel(ByVal path As String)
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")


        For i = 0 To dgvData.RowCount - 2
            For j = 0 To dgvData.ColumnCount - 1
                For k As Integer = 1 To dgvData.Columns.Count
                    xlWorkSheet.Cells(1, k) = dgvData.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = dgvData(j, i).Value.ToString()
                Next
            Next
        Next

        xlWorkSheet.SaveAs(path)
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Bạn có thể xem File tại địa chỉ: " + path)
    End Sub

    Private Sub btnBrowse_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click
        If (dgvData.Rows.Count() < 1) Then
            MessageBox.Show("Bạn Chưa LoadData")
            Return
        End If
        Dim sfd As New SaveFileDialog()
        sfd.Filter = "Excel File (*.xlsx)|*.xlsx|All File(*.*)|*.*"
        If (sfd.ShowDialog() = DialogResult.OK) Then
            Dim path As String = sfd.FileName
            ExportGridToExcel(path)
        End If
    End Sub

    Private Sub btnLoad_Click(sender As System.Object, e As System.EventArgs) Handles btnLoad.Click
        LoadData(cmbTableName.SelectedValue.ToString.Trim())
        lbTenBang.Text = cmbTableName.SelectedValue.ToString()
    End Sub
    Private Sub UCExport_KeyDown(sender As System.Object, e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
        If (e.KeyCode = Keys.Enter) Then
            btnExport.PerformClick()
        End If
    End Sub
End Class
